package com.machineapps.botesom.repository;

import java.util.ArrayList;
import java.util.List;

import android.content.Context;
import android.database.Cursor;
import android.database.SQLException;
import android.database.sqlite.SQLiteQueryBuilder;
import android.util.Log;

import com.machineapps.botesom.BoteSomConstants;
import com.machineapps.botesom.model.Button;
import com.machineapps.botesom.model.Category;
import com.machineapps.framework.repository.RepositorySQLite;

public class CategoryRepositorySQLite extends RepositorySQLite<Category> implements CategoryRepository {

	public static final String TABLE_BUTTON_CATEGORY = "Button_Category";

	public CategoryRepositorySQLite(Context context) {
		super(context, BoteSomConstants.DB_NAME, Context.MODE_PRIVATE);
	}

	// Performs a Query in a Category
	public Cursor query(SQLiteQueryBuilder queryBuilder, String[] projection, String selection, String[] selectionArgs,
			String groupBy, String having, String orderBy) {
		Cursor c = queryBuilder.query(getDatabase(), projection, selection, selectionArgs, groupBy, having, orderBy);
		return c;
	}

	/*
	 * (non-Javadoc)
	 * 
	 * @see com.machineapps.botesom.repository.CategoryRepository#getButtons(long)
	 */
	@Override
	public List<Button> getButtonsFromCategory(long categoryId) {
		List<Button> buttons = new ArrayList<Button>();
		Cursor c = null;

		try {
			String sql = "SELECT button._id, button.name, button.sound_file_name, button.image_file_name FROM Button button "
					+ " LEFT OUTER JOIN Button_Category bc ON bc.id_button = button._id "
					+ " LEFT OUTER JOIN Category category ON bc.id_category = category._id "
					+ " WHERE category._id = ? ORDER BY button.name;";

			c = getDatabase().rawQuery(sql, new String[] { categoryId + "" });

			// if found
			while (c.moveToNext()) {

				Button button = new Button();

				button.setId(c.getLong(0));
				button.setName(c.getString(1));
				button.setSoundFileName(c.getString(2));
				button.setImageFileName(c.getString(3));

				buttons.add(button);
			}
		} catch (SQLException e) {
			Log.e(BoteSomConstants.CATEGORY, "Error while querying for Buttons in category by Id=" + categoryId + " : "
					+ e.toString());
			return null;
		} finally {
			if (c != null && !c.isClosed()) {
				c.close();
			}
		}

		return buttons;
	}

	/*
	 * (non-Javadoc)
	 * 
	 * @see com.machineapps.botesom.repository.CategoryRepository#addButtonToCategory(long, long)
	 */
	@Override
	public void addButtonToCategory(long buttonId, long categoryId) {
		List<Button> buttons = new ArrayList<Button>();
		Cursor c = null;

		try {
			String sql = "INSERT Into Button_Category (id_button, id_category) values(?,?);";

			c = getDatabase().rawQuery(sql, new String[] { buttonId + "", categoryId + "" });

			// if found
			if (c.moveToNext()) {

				Button button = new Button();

				button.setId(c.getLong(0));
				button.setName(c.getString(1));

				buttons.add(button);
			}
		} catch (SQLException e) {
			Log.e(BoteSomConstants.CATEGORY, "Error while querying for Buttons in category by Id=" + categoryId + " : "
					+ e.toString());
		} finally {
			if (c != null && !c.isClosed()) {
				c.close();
			}
		}

	}
	
	public void removeButtonFromCategory(long buttonId, long categoryId) {
		try {
			String whereClause = "id_button = ? and id_category = ?";
			String[] whereArgs = new String[]{buttonId + "", categoryId + ""};

			getDatabase().delete("button_category", whereClause, whereArgs);

		} catch (SQLException e) {
			Log.e(BoteSomConstants.CATEGORY, "Error while delete button Id=" + buttonId + " , from category Id=" + categoryId + " : "
					+ e.toString());
		} 
	}

	public boolean existsButtonInCategory(long buttonId, long categoryId) {
		boolean exists = false;
		Cursor c = null;

		try {
			String sql = "SELECT button._id FROM Button button "
					+ " LEFT OUTER JOIN Button_Category bc ON bc.id_button = button._id "
					+ " LEFT OUTER JOIN Category category ON bc.id_category = category._id "
					+ " WHERE category._id = ? and button._id = ?;";

			c = getDatabase().rawQuery(sql, new String[] { categoryId + "", buttonId + "" });

			exists = c.getCount() > 0;
			// if found
		} catch (SQLException e) {
			Log.e(BoteSomConstants.CATEGORY, "Error while querying for number of buttons in category by Id=" + categoryId + " : "
					+ e.toString());
		} finally {
			if (c != null && !c.isClosed()) {
				c.close();
			}
		}

		return exists;
	}
	@Override
	public void createCategory(Category category) {
		save(category);
	}
}
